﻿namespace AnyOffice.DAL
{
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class HrmsDao
    {
        public bool Delete(SqlConnection conn, int hrmsId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("DELETE FROM [dbo].[Hrms] ");
            builder.Append("WHERE ");
            builder.Append("\t[HrmsId] = @HrmsId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@HrmsId", SqlDbType.Int) };
            commandParameters[0].Value = hrmsId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public int Insert(SqlConnection conn, HrmsData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("INSERT INTO [dbo].[Hrms] ");
            builder.Append("( ");
            builder.Append("\t[UserId], ");
            builder.Append("\t[UserName], ");
            builder.Append("\t[DeptId], ");
            builder.Append("\t[Number], ");
            builder.Append("\t[CardNo], ");
            builder.Append("\t[Sex], ");
            builder.Append("\t[Birthday], ");
            builder.Append("\t[Nation], ");
            builder.Append("\t[Marry], ");
            builder.Append("\t[NativePlace], ");
            builder.Append("\t[HukouPlace], ");
            builder.Append("\t[Education], ");
            builder.Append("\t[Speciallity], ");
            builder.Append("\t[School], ");
            builder.Append("\t[WorkDate], ");
            builder.Append("\t[JoinDate], ");
            builder.Append("\t[Department], ");
            builder.Append("\t[Post], ");
            builder.Append("\t[TechPost], ");
            builder.Append("\t[Politics], ");
            builder.Append("\t[HomeAddr], ");
            builder.Append("\t[HomeTel], ");
            builder.Append("\t[Email], ");
            builder.Append("\t[ContractType], ");
            builder.Append("\t[ContractDate1], ");
            builder.Append("\t[ContractDate2], ");
            builder.Append("\t[LeaveType], ");
            builder.Append("\t[PostChange], ");
            builder.Append("\t[Edu], ");
            builder.Append("\t[Resume], ");
            builder.Append("\t[Sociaty], ");
            builder.Append("\t[Reward], ");
            builder.Append("\t[Certificate], ");
            builder.Append("\t[Train], ");
            builder.Append("\t[Surety], ");
            builder.Append("\t[Contract], ");
            builder.Append("\t[Insure], ");
            builder.Append("\t[BodyExamim], ");
            builder.Append("\t[Memo], ");
            builder.Append("\t[Photo], ");
            builder.Append("\t[AttachmentId] ");
            builder.Append(") ");
            builder.Append("VALUES ");
            builder.Append("( ");
            builder.Append("\t@UserId, ");
            builder.Append("\t@UserName, ");
            builder.Append("\t@DeptId, ");
            builder.Append("\t@Number, ");
            builder.Append("\t@CardNo, ");
            builder.Append("\t@Sex, ");
            builder.Append("\t@Birthday, ");
            builder.Append("\t@Nation, ");
            builder.Append("\t@Marry, ");
            builder.Append("\t@NativePlace, ");
            builder.Append("\t@HukouPlace, ");
            builder.Append("\t@Education, ");
            builder.Append("\t@Speciallity, ");
            builder.Append("\t@School, ");
            builder.Append("\t@WorkDate, ");
            builder.Append("\t@JoinDate, ");
            builder.Append("\t@Department, ");
            builder.Append("\t@Post, ");
            builder.Append("\t@TechPost, ");
            builder.Append("\t@Politics, ");
            builder.Append("\t@HomeAddr, ");
            builder.Append("\t@HomeTel, ");
            builder.Append("\t@Email, ");
            builder.Append("\t@ContractType, ");
            builder.Append("\t@ContractDate1, ");
            builder.Append("\t@ContractDate2, ");
            builder.Append("\t@LeaveType, ");
            builder.Append("\t@PostChange, ");
            builder.Append("\t@Edu, ");
            builder.Append("\t@Resume, ");
            builder.Append("\t@Sociaty, ");
            builder.Append("\t@Reward, ");
            builder.Append("\t@Certificate, ");
            builder.Append("\t@Train, ");
            builder.Append("\t@Surety, ");
            builder.Append("\t@Contract, ");
            builder.Append("\t@Insure, ");
            builder.Append("\t@BodyExamim, ");
            builder.Append("\t@Memo, ");
            builder.Append("\t@Photo, ");
            builder.Append("\t@AttachmentId ");
            builder.Append(") ");
            builder.Append("SELECT SCOPE_IDENTITY() AS RetValue ");
            SqlParameter[] commandParameters = new SqlParameter[0x29];
            commandParameters[0] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            if (data.UserIdIsNull)
            {
                commandParameters[0].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0].Value = data.UserId;
            }
            commandParameters[1] = new SqlParameter("@UserName", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = data.UserName;
            commandParameters[2] = new SqlParameter("@DeptId", SqlDbType.Int);
            commandParameters[2].Value = data.DeptId;
            commandParameters[3] = new SqlParameter("@Number", SqlDbType.NVarChar, 50);
            if (data.NumberIsNull)
            {
                commandParameters[3].Value = DBNull.Value;
            }
            else
            {
                commandParameters[3].Value = data.Number;
            }
            commandParameters[4] = new SqlParameter("@CardNo", SqlDbType.NVarChar, 50);
            if (data.CardNoIsNull)
            {
                commandParameters[4].Value = DBNull.Value;
            }
            else
            {
                commandParameters[4].Value = data.CardNo;
            }
            commandParameters[5] = new SqlParameter("@Sex", SqlDbType.Bit);
            commandParameters[5].Value = data.Sex;
            commandParameters[6] = new SqlParameter("@Birthday", SqlDbType.DateTime);
            if (data.BirthdayIsNull)
            {
                commandParameters[6].Value = DBNull.Value;
            }
            else
            {
                commandParameters[6].Value = data.Birthday;
            }
            commandParameters[7] = new SqlParameter("@Nation", SqlDbType.NVarChar, 50);
            if (data.NationIsNull)
            {
                commandParameters[7].Value = DBNull.Value;
            }
            else
            {
                commandParameters[7].Value = data.Nation;
            }
            commandParameters[8] = new SqlParameter("@Marry", SqlDbType.NVarChar, 50);
            if (data.MarryIsNull)
            {
                commandParameters[8].Value = DBNull.Value;
            }
            else
            {
                commandParameters[8].Value = data.Marry;
            }
            commandParameters[9] = new SqlParameter("@NativePlace", SqlDbType.NVarChar, 50);
            if (data.NativePlaceIsNull)
            {
                commandParameters[9].Value = DBNull.Value;
            }
            else
            {
                commandParameters[9].Value = data.NativePlace;
            }
            commandParameters[10] = new SqlParameter("@HukouPlace", SqlDbType.NVarChar, 50);
            if (data.HukouPlaceIsNull)
            {
                commandParameters[10].Value = DBNull.Value;
            }
            else
            {
                commandParameters[10].Value = data.HukouPlace;
            }
            commandParameters[11] = new SqlParameter("@Education", SqlDbType.NVarChar, 50);
            if (data.EducationIsNull)
            {
                commandParameters[11].Value = DBNull.Value;
            }
            else
            {
                commandParameters[11].Value = data.Education;
            }
            commandParameters[12] = new SqlParameter("@Speciallity", SqlDbType.NVarChar, 50);
            if (data.SpeciallityIsNull)
            {
                commandParameters[12].Value = DBNull.Value;
            }
            else
            {
                commandParameters[12].Value = data.Speciallity;
            }
            commandParameters[13] = new SqlParameter("@School", SqlDbType.NVarChar, 50);
            if (data.SchoolIsNull)
            {
                commandParameters[13].Value = DBNull.Value;
            }
            else
            {
                commandParameters[13].Value = data.School;
            }
            commandParameters[14] = new SqlParameter("@WorkDate", SqlDbType.DateTime);
            if (data.WorkDateIsNull)
            {
                commandParameters[14].Value = DBNull.Value;
            }
            else
            {
                commandParameters[14].Value = data.WorkDate;
            }
            commandParameters[15] = new SqlParameter("@JoinDate", SqlDbType.DateTime);
            if (data.JoinDateIsNull)
            {
                commandParameters[15].Value = DBNull.Value;
            }
            else
            {
                commandParameters[15].Value = data.JoinDate;
            }
            commandParameters[0x10] = new SqlParameter("@Department", SqlDbType.NVarChar, 50);
            if (data.DepartmentIsNull)
            {
                commandParameters[0x10].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x10].Value = data.Department;
            }
            commandParameters[0x11] = new SqlParameter("@Post", SqlDbType.NVarChar, 50);
            if (data.PostIsNull)
            {
                commandParameters[0x11].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x11].Value = data.Post;
            }
            commandParameters[0x12] = new SqlParameter("@TechPost", SqlDbType.NVarChar, 50);
            if (data.TechPostIsNull)
            {
                commandParameters[0x12].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x12].Value = data.TechPost;
            }
            commandParameters[0x13] = new SqlParameter("@Politics", SqlDbType.NVarChar, 50);
            if (data.PoliticsIsNull)
            {
                commandParameters[0x13].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x13].Value = data.Politics;
            }
            commandParameters[20] = new SqlParameter("@HomeAddr", SqlDbType.NVarChar, 200);
            if (data.HomeAddrIsNull)
            {
                commandParameters[20].Value = DBNull.Value;
            }
            else
            {
                commandParameters[20].Value = data.HomeAddr;
            }
            commandParameters[0x15] = new SqlParameter("@HomeTel", SqlDbType.NVarChar, 50);
            if (data.HomeTelIsNull)
            {
                commandParameters[0x15].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x15].Value = data.HomeTel;
            }
            commandParameters[0x16] = new SqlParameter("@Email", SqlDbType.NVarChar, 200);
            if (data.EmailIsNull)
            {
                commandParameters[0x16].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x16].Value = data.Email;
            }
            commandParameters[0x17] = new SqlParameter("@ContractType", SqlDbType.NVarChar, 50);
            if (data.ContractTypeIsNull)
            {
                commandParameters[0x17].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x17].Value = data.ContractType;
            }
            commandParameters[0x18] = new SqlParameter("@ContractDate1", SqlDbType.DateTime);
            if (data.ContractDate1IsNull)
            {
                commandParameters[0x18].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x18].Value = data.ContractDate1;
            }
            commandParameters[0x19] = new SqlParameter("@ContractDate2", SqlDbType.DateTime);
            if (data.ContractDate2IsNull)
            {
                commandParameters[0x19].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x19].Value = data.ContractDate2;
            }
            commandParameters[0x1a] = new SqlParameter("@LeaveType", SqlDbType.NVarChar, 50);
            if (data.LeaveTypeIsNull)
            {
                commandParameters[0x1a].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x1a].Value = data.LeaveType;
            }
            commandParameters[0x1b] = new SqlParameter("@PostChange", SqlDbType.NText);
            if (data.PostChangeIsNull)
            {
                commandParameters[0x1b].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x1b].Value = data.PostChange;
            }
            commandParameters[0x1c] = new SqlParameter("@Edu", SqlDbType.NText);
            if (data.EduIsNull)
            {
                commandParameters[0x1c].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x1c].Value = data.Edu;
            }
            commandParameters[0x1d] = new SqlParameter("@Resume", SqlDbType.NText);
            if (data.ResumeIsNull)
            {
                commandParameters[0x1d].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x1d].Value = data.Resume;
            }
            commandParameters[30] = new SqlParameter("@Sociaty", SqlDbType.NText);
            if (data.SociatyIsNull)
            {
                commandParameters[30].Value = DBNull.Value;
            }
            else
            {
                commandParameters[30].Value = data.Sociaty;
            }
            commandParameters[0x1f] = new SqlParameter("@Reward", SqlDbType.NText);
            if (data.RewardIsNull)
            {
                commandParameters[0x1f].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x1f].Value = data.Reward;
            }
            commandParameters[0x20] = new SqlParameter("@Certificate", SqlDbType.NText);
            if (data.CertificateIsNull)
            {
                commandParameters[0x20].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x20].Value = data.Certificate;
            }
            commandParameters[0x21] = new SqlParameter("@Train", SqlDbType.NText);
            if (data.TrainIsNull)
            {
                commandParameters[0x21].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x21].Value = data.Train;
            }
            commandParameters[0x22] = new SqlParameter("@Surety", SqlDbType.NText);
            if (data.SuretyIsNull)
            {
                commandParameters[0x22].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x22].Value = data.Surety;
            }
            commandParameters[0x23] = new SqlParameter("@Contract", SqlDbType.NText);
            if (data.ContractIsNull)
            {
                commandParameters[0x23].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x23].Value = data.Contract;
            }
            commandParameters[0x24] = new SqlParameter("@Insure", SqlDbType.NText);
            if (data.InsureIsNull)
            {
                commandParameters[0x24].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x24].Value = data.Insure;
            }
            commandParameters[0x25] = new SqlParameter("@BodyExamim", SqlDbType.NText);
            if (data.BodyExamimIsNull)
            {
                commandParameters[0x25].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x25].Value = data.BodyExamim;
            }
            commandParameters[0x26] = new SqlParameter("@Memo", SqlDbType.NText);
            if (data.MemoIsNull)
            {
                commandParameters[0x26].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x26].Value = data.Memo;
            }
            commandParameters[0x27] = new SqlParameter("@Photo", SqlDbType.Image);
            if (data.PhotoIsNull)
            {
                commandParameters[0x27].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x27].Value = data.Photo;
            }
            commandParameters[40] = new SqlParameter("@AttachmentId", SqlDbType.NVarChar, 50);
            if (data.AttachmentIdIsNull)
            {
                commandParameters[40].Value = DBNull.Value;
            }
            else
            {
                commandParameters[40].Value = data.AttachmentId;
            }
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, builder.ToString(), commandParameters);
            if (obj2 != null)
            {
                return Convert.ToInt32(obj2);
            }
            return -1;
        }

        public DataTable SelectByDeptId(SqlConnection conn, int deptId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT [HrmsId],[UserId],[UserName],[Sex],[Birthday],[Nation],[Marry],[NativePlace],[Politics],[CardNo] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Hrms] ");
            builder.Append("WHERE ");
            builder.Append("\t[DeptId] = @DeptId ");
            builder.Append("ORDER BY [HrmsId] ASC");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@DeptId", SqlDbType.Int) };
            commandParameters[0].Value = deptId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Hrms";
            return set.Tables[0];
        }

        public DataTable SelectByHrmsId(SqlConnection conn, int hrmsId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[HrmsId], ");
            builder.Append("\t[UserId], ");
            builder.Append("\t[UserName], ");
            builder.Append("\t[DeptId], ");
            builder.Append("\t[Number], ");
            builder.Append("\t[CardNo], ");
            builder.Append("\t[Sex], ");
            builder.Append("\t[Birthday], ");
            builder.Append("\t[Nation], ");
            builder.Append("\t[Marry], ");
            builder.Append("\t[NativePlace], ");
            builder.Append("\t[HukouPlace], ");
            builder.Append("\t[Education], ");
            builder.Append("\t[Speciallity], ");
            builder.Append("\t[School], ");
            builder.Append("\t[WorkDate], ");
            builder.Append("\t[JoinDate], ");
            builder.Append("\t[Department], ");
            builder.Append("\t[Post], ");
            builder.Append("\t[TechPost], ");
            builder.Append("\t[Politics], ");
            builder.Append("\t[HomeAddr], ");
            builder.Append("\t[HomeTel], ");
            builder.Append("\t[Email], ");
            builder.Append("\t[ContractType], ");
            builder.Append("\t[ContractDate1], ");
            builder.Append("\t[ContractDate2], ");
            builder.Append("\t[LeaveType], ");
            builder.Append("\t[PostChange], ");
            builder.Append("\t[Edu], ");
            builder.Append("\t[Resume], ");
            builder.Append("\t[Sociaty], ");
            builder.Append("\t[Reward], ");
            builder.Append("\t[Certificate], ");
            builder.Append("\t[Train], ");
            builder.Append("\t[Surety], ");
            builder.Append("\t[Contract], ");
            builder.Append("\t[Insure], ");
            builder.Append("\t[BodyExamim], ");
            builder.Append("\t[Memo], ");
            builder.Append("\tSUBSTRING([Photo],1,10) AS Photo, ");
            builder.Append("\t[AttachmentId] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Hrms] ");
            builder.Append("WHERE ");
            builder.Append("\t[HrmsId] = @HrmsId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@HrmsId", SqlDbType.Int) };
            commandParameters[0].Value = hrmsId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Hrms";
            return set.Tables[0];
        }

        public DataTable SelectByUserId(SqlConnection conn, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT ");
            builder.Append("\t[HrmsId], ");
            builder.Append("\t[UserId], ");
            builder.Append("\t[UserName], ");
            builder.Append("\t[DeptId], ");
            builder.Append("\t[Number], ");
            builder.Append("\t[CardNo], ");
            builder.Append("\t[Sex], ");
            builder.Append("\t[Birthday], ");
            builder.Append("\t[Nation], ");
            builder.Append("\t[Marry], ");
            builder.Append("\t[NativePlace], ");
            builder.Append("\t[HukouPlace], ");
            builder.Append("\t[Education], ");
            builder.Append("\t[Speciallity], ");
            builder.Append("\t[School], ");
            builder.Append("\t[WorkDate], ");
            builder.Append("\t[JoinDate], ");
            builder.Append("\t[Department], ");
            builder.Append("\t[Post], ");
            builder.Append("\t[TechPost], ");
            builder.Append("\t[Politics], ");
            builder.Append("\t[HomeAddr], ");
            builder.Append("\t[HomeTel], ");
            builder.Append("\t[Email], ");
            builder.Append("\t[ContractType], ");
            builder.Append("\t[ContractDate1], ");
            builder.Append("\t[ContractDate2], ");
            builder.Append("\t[LeaveType], ");
            builder.Append("\t[PostChange], ");
            builder.Append("\t[Edu], ");
            builder.Append("\t[Resume], ");
            builder.Append("\t[Sociaty], ");
            builder.Append("\t[Reward], ");
            builder.Append("\t[Certificate], ");
            builder.Append("\t[Train], ");
            builder.Append("\t[Surety], ");
            builder.Append("\t[Contract], ");
            builder.Append("\t[Insure], ");
            builder.Append("\t[BodyExamim], ");
            builder.Append("\t[Memo], ");
            builder.Append("\tSUBSTRING([Photo],1,10) AS Photo, ");
            builder.Append("\t[AttachmentId] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Hrms] ");
            builder.Append("WHERE ");
            builder.Append("\t[UserId] = @UserId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@UserId", SqlDbType.NVarChar) };
            commandParameters[0].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Hrms";
            return set.Tables[0];
        }

        public DataTable StatisticForAge(SqlConnection conn)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("DECLARE @StatisticForAge TABLE(Num int,Title varchar(20)) ");
            builder.Append("INSERT INTO @StatisticForAge SELECT COUNT([HrmsId]),'25岁以下' FROM [dbo].[Hrms] ");
            builder.Append("\tWHERE\tDATEDIFF(year,[Birthday],getdate()) <= 25 AND [Birthday] IS NOT NULL ");
            builder.Append("INSERT INTO @StatisticForAge SELECT COUNT([HrmsId]),'26-30岁' FROM [dbo].[Hrms]  ");
            builder.Append("\tWHERE\t(DATEDIFF(year,[Birthday],getdate()) > 25)  ");
            builder.Append("\t\tAND (DATEDIFF(year,[Birthday],getdate()) <= 30) ");
            builder.Append("\t\tAND [Birthday] IS NOT NULL ");
            builder.Append("INSERT INTO @StatisticForAge SELECT COUNT([HrmsId]),'31-35岁' FROM [dbo].[Hrms]  ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t(DATEDIFF(year,[Birthday],getdate()) > 30)  ");
            builder.Append("\t\tAND (DATEDIFF(year,[Birthday],getdate()) <= 35) ");
            builder.Append("\t\tAND [Birthday] IS NOT NULL ");
            builder.Append("INSERT INTO @StatisticForAge SELECT COUNT([HrmsId]),'36-40岁' FROM [dbo].[Hrms]  ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t(DATEDIFF(year,[Birthday],getdate()) > 35)  ");
            builder.Append("\t\tAND (DATEDIFF(year,[Birthday],getdate()) <= 40) ");
            builder.Append("\t\tAND [Birthday] IS NOT NULL ");
            builder.Append("INSERT INTO @StatisticForAge SELECT COUNT([HrmsId]),'41-45岁' FROM [dbo].[Hrms]  ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t(DATEDIFF(year,[Birthday],getdate()) > 40)  ");
            builder.Append("\t\tAND (DATEDIFF(year,[Birthday],getdate()) <= 45) ");
            builder.Append("\t\tAND [Birthday] IS NOT NULL ");
            builder.Append("INSERT INTO @StatisticForAge SELECT COUNT([HrmsId]),'46-50岁' FROM [dbo].[Hrms]  ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t(DATEDIFF(year,[Birthday],getdate()) > 45)  ");
            builder.Append("\t\tAND (DATEDIFF(year,[Birthday],getdate()) <= 50) ");
            builder.Append("\t\tAND [Birthday] IS NOT NULL ");
            builder.Append("INSERT INTO @StatisticForAge SELECT COUNT([HrmsId]),'51-55岁' FROM [dbo].[Hrms]  ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t(DATEDIFF(year,[Birthday],getdate()) > 50)  ");
            builder.Append("\t\tAND (DATEDIFF(year,[Birthday],getdate()) <= 55) ");
            builder.Append("\t\tAND [Birthday] IS NOT NULL ");
            builder.Append("INSERT INTO @StatisticForAge SELECT COUNT([HrmsId]),'56-60岁' FROM [dbo].[Hrms]  ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t(DATEDIFF(year,[Birthday],getdate()) > 55)  ");
            builder.Append("\t\tAND (DATEDIFF(year,[Birthday],getdate()) <= 60) ");
            builder.Append("\t\tAND [Birthday] IS NOT NULL ");
            builder.Append("INSERT INTO @StatisticForAge SELECT COUNT([HrmsId]),'60岁以上' FROM [dbo].[Hrms]  ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t(DATEDIFF(year,[Birthday],getdate()) > 60) ");
            builder.Append("\t\tAND [Birthday] IS NOT NULL ");
            builder.Append("INSERT INTO @StatisticForAge SELECT COUNT([HrmsId]),'未知' FROM [dbo].[Hrms] WHERE [Birthday] IS NULL ");
            builder.Append("SELECT * FROM @StatisticForAge ");
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString());
            set.Tables[0].TableName = "Hrms";
            return set.Tables[0];
        }

        public DataTable StatisticForEducation(SqlConnection conn)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("DECLARE @StatisticForEducation TABLE(Num int,Title varchar(20)) ");
            builder.Append("INSERT INTO @StatisticForEducation SELECT COUNT([HrmsId]),'小学' FROM [dbo].[Hrms] WHERE [Education] = '小学' ");
            builder.Append("INSERT INTO @StatisticForEducation SELECT COUNT([HrmsId]),'初中' FROM [dbo].[Hrms] WHERE [Education] = '初中' ");
            builder.Append("INSERT INTO @StatisticForEducation SELECT COUNT([HrmsId]),'高中' FROM [dbo].[Hrms] WHERE [Education] = '高中' ");
            builder.Append("INSERT INTO @StatisticForEducation SELECT COUNT([HrmsId]),'中专' FROM [dbo].[Hrms] WHERE [Education] = '中专' ");
            builder.Append("INSERT INTO @StatisticForEducation SELECT COUNT([HrmsId]),'大专' FROM [dbo].[Hrms] WHERE [Education] = '大专' ");
            builder.Append("INSERT INTO @StatisticForEducation SELECT COUNT([HrmsId]),'大本' FROM [dbo].[Hrms] WHERE [Education] = '大本' ");
            builder.Append("INSERT INTO @StatisticForEducation SELECT COUNT([HrmsId]),'硕士' FROM [dbo].[Hrms] WHERE [Education] = '硕士' ");
            builder.Append("INSERT INTO @StatisticForEducation SELECT COUNT([HrmsId]),'博士' FROM [dbo].[Hrms] WHERE [Education] = '博士' ");
            builder.Append("INSERT INTO @StatisticForEducation SELECT COUNT([HrmsId]),'博士后' FROM [dbo].[Hrms] WHERE [Education] = '博士后' ");
            builder.Append("INSERT INTO @StatisticForEducation SELECT COUNT([HrmsId]),'未知' FROM [dbo].[Hrms] WHERE ISNULL([Education],'') = '' ");
            builder.Append("SELECT * FROM @StatisticForEducation ");
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString());
            set.Tables[0].TableName = "Hrms";
            return set.Tables[0];
        }

        public DataTable StatisticForPolitics(SqlConnection conn)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("DECLARE @StatisticForPolitics TABLE(Num int,Title varchar(20)) ");
            builder.Append("INSERT INTO @StatisticForPolitics SELECT COUNT([HrmsId]),'群众' FROM [dbo].[Hrms] WHERE [Politics]='群众' ");
            builder.Append("INSERT INTO @StatisticForPolitics SELECT COUNT([HrmsId]),'团员' FROM [dbo].[Hrms] WHERE [Politics]='团员' ");
            builder.Append("INSERT INTO @StatisticForPolitics SELECT COUNT([HrmsId]),'预备党员' FROM [dbo].[Hrms] WHERE [Politics]='预备党员' ");
            builder.Append("INSERT INTO @StatisticForPolitics SELECT COUNT([HrmsId]),'党员' FROM [dbo].[Hrms] WHERE [Politics]='党员' ");
            builder.Append("INSERT INTO @StatisticForPolitics SELECT COUNT([HrmsId]),'未知' FROM [dbo].[Hrms] WHERE ISNULL([Politics],'')='' ");
            builder.Append("SELECT * FROM @StatisticForPolitics ");
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString());
            set.Tables[0].TableName = "Hrms";
            return set.Tables[0];
        }

        public DataTable StatisticForSex(SqlConnection conn)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("DECLARE @StatisticForSex TABLE(Num int,Title varchar(20)) ");
            builder.Append("INSERT INTO @StatisticForSex SELECT COUNT([HrmsId]),'男' FROM [dbo].[Hrms] WHERE [Sex]=1 ");
            builder.Append("INSERT INTO @StatisticForSex SELECT COUNT([HrmsId]),'女' FROM [dbo].[Hrms] WHERE [Sex]=0 ");
            builder.Append("SELECT * FROM @StatisticForSex ");
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString());
            set.Tables[0].TableName = "Hrms";
            return set.Tables[0];
        }

        public bool Update(SqlConnection conn, HrmsData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE [dbo].[Hrms] SET ");
            builder.Append("\t[UserId] = @UserId, ");
            builder.Append("\t[UserName] = @UserName, ");
            builder.Append("\t[DeptId] = @DeptId, ");
            builder.Append("\t[Number] = @Number, ");
            builder.Append("\t[CardNo] = @CardNo, ");
            builder.Append("\t[Sex] = @Sex, ");
            builder.Append("\t[Birthday] = @Birthday, ");
            builder.Append("\t[Nation] = @Nation, ");
            builder.Append("\t[Marry] = @Marry, ");
            builder.Append("\t[NativePlace] = @NativePlace, ");
            builder.Append("\t[HukouPlace] = @HukouPlace, ");
            builder.Append("\t[Education] = @Education, ");
            builder.Append("\t[Speciallity] = @Speciallity, ");
            builder.Append("\t[School] = @School, ");
            builder.Append("\t[WorkDate] = @WorkDate, ");
            builder.Append("\t[JoinDate] = @JoinDate, ");
            builder.Append("\t[Department] = @Department, ");
            builder.Append("\t[Post] = @Post, ");
            builder.Append("\t[TechPost] = @TechPost, ");
            builder.Append("\t[Politics] = @Politics, ");
            builder.Append("\t[HomeAddr] = @HomeAddr, ");
            builder.Append("\t[HomeTel] = @HomeTel, ");
            builder.Append("\t[Email] = @Email, ");
            builder.Append("\t[ContractType] = @ContractType, ");
            builder.Append("\t[ContractDate1] = @ContractDate1, ");
            builder.Append("\t[ContractDate2] = @ContractDate2, ");
            builder.Append("\t[LeaveType] = @LeaveType, ");
            builder.Append("\t[PostChange] = @PostChange, ");
            builder.Append("\t[Edu] = @Edu, ");
            builder.Append("\t[Resume] = @Resume, ");
            builder.Append("\t[Sociaty] = @Sociaty, ");
            builder.Append("\t[Reward] = @Reward, ");
            builder.Append("\t[Certificate] = @Certificate, ");
            builder.Append("\t[Train] = @Train, ");
            builder.Append("\t[Surety] = @Surety, ");
            builder.Append("\t[Contract] = @Contract, ");
            builder.Append("\t[Insure] = @Insure, ");
            builder.Append("\t[BodyExamim] = @BodyExamim, ");
            builder.Append("\t[Memo] = @Memo, ");
            builder.Append("\t[AttachmentId] = @AttachmentId ");
            builder.Append("WHERE ");
            builder.Append("\t[HrmsId] = @HrmsId ");
            builder.Append("IF @Photo IS NOT NULL ");
            builder.Append("\tUPDATE [dbo].[Hrms] ");
            builder.Append("\tSET ");
            builder.Append("\t\t[Photo] = @Photo ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[HrmsId] = @HrmsId ");
            SqlParameter[] commandParameters = new SqlParameter[0x2a];
            commandParameters[0] = new SqlParameter("@HrmsId", SqlDbType.Int);
            commandParameters[0].Value = data.HrmsId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            if (data.UserIdIsNull)
            {
                commandParameters[1].Value = DBNull.Value;
            }
            else
            {
                commandParameters[1].Value = data.UserId;
            }
            commandParameters[2] = new SqlParameter("@UserName", SqlDbType.NVarChar, 20);
            commandParameters[2].Value = data.UserName;
            commandParameters[3] = new SqlParameter("@DeptId", SqlDbType.Int);
            commandParameters[3].Value = data.DeptId;
            commandParameters[4] = new SqlParameter("@Number", SqlDbType.NVarChar, 50);
            if (data.NumberIsNull)
            {
                commandParameters[4].Value = DBNull.Value;
            }
            else
            {
                commandParameters[4].Value = data.Number;
            }
            commandParameters[5] = new SqlParameter("@CardNo", SqlDbType.NVarChar, 50);
            if (data.CardNoIsNull)
            {
                commandParameters[5].Value = DBNull.Value;
            }
            else
            {
                commandParameters[5].Value = data.CardNo;
            }
            commandParameters[6] = new SqlParameter("@Sex", SqlDbType.Bit);
            commandParameters[6].Value = data.Sex;
            commandParameters[7] = new SqlParameter("@Birthday", SqlDbType.DateTime);
            if (data.BirthdayIsNull)
            {
                commandParameters[7].Value = DBNull.Value;
            }
            else
            {
                commandParameters[7].Value = data.Birthday;
            }
            commandParameters[8] = new SqlParameter("@Nation", SqlDbType.NVarChar, 50);
            if (data.NationIsNull)
            {
                commandParameters[8].Value = DBNull.Value;
            }
            else
            {
                commandParameters[8].Value = data.Nation;
            }
            commandParameters[9] = new SqlParameter("@Marry", SqlDbType.NVarChar, 50);
            if (data.MarryIsNull)
            {
                commandParameters[9].Value = DBNull.Value;
            }
            else
            {
                commandParameters[9].Value = data.Marry;
            }
            commandParameters[10] = new SqlParameter("@NativePlace", SqlDbType.NVarChar, 50);
            if (data.NativePlaceIsNull)
            {
                commandParameters[10].Value = DBNull.Value;
            }
            else
            {
                commandParameters[10].Value = data.NativePlace;
            }
            commandParameters[11] = new SqlParameter("@HukouPlace", SqlDbType.NVarChar, 50);
            if (data.HukouPlaceIsNull)
            {
                commandParameters[11].Value = DBNull.Value;
            }
            else
            {
                commandParameters[11].Value = data.HukouPlace;
            }
            commandParameters[12] = new SqlParameter("@Education", SqlDbType.NVarChar, 50);
            if (data.EducationIsNull)
            {
                commandParameters[12].Value = DBNull.Value;
            }
            else
            {
                commandParameters[12].Value = data.Education;
            }
            commandParameters[13] = new SqlParameter("@Speciallity", SqlDbType.NVarChar, 50);
            if (data.SpeciallityIsNull)
            {
                commandParameters[13].Value = DBNull.Value;
            }
            else
            {
                commandParameters[13].Value = data.Speciallity;
            }
            commandParameters[14] = new SqlParameter("@School", SqlDbType.NVarChar, 50);
            if (data.SchoolIsNull)
            {
                commandParameters[14].Value = DBNull.Value;
            }
            else
            {
                commandParameters[14].Value = data.School;
            }
            commandParameters[15] = new SqlParameter("@WorkDate", SqlDbType.DateTime);
            if (data.WorkDateIsNull)
            {
                commandParameters[15].Value = DBNull.Value;
            }
            else
            {
                commandParameters[15].Value = data.WorkDate;
            }
            commandParameters[0x10] = new SqlParameter("@JoinDate", SqlDbType.DateTime);
            if (data.JoinDateIsNull)
            {
                commandParameters[0x10].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x10].Value = data.JoinDate;
            }
            commandParameters[0x11] = new SqlParameter("@Department", SqlDbType.NVarChar, 50);
            if (data.DepartmentIsNull)
            {
                commandParameters[0x11].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x11].Value = data.Department;
            }
            commandParameters[0x12] = new SqlParameter("@Post", SqlDbType.NVarChar, 50);
            if (data.PostIsNull)
            {
                commandParameters[0x12].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x12].Value = data.Post;
            }
            commandParameters[0x13] = new SqlParameter("@TechPost", SqlDbType.NVarChar, 50);
            if (data.TechPostIsNull)
            {
                commandParameters[0x13].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x13].Value = data.TechPost;
            }
            commandParameters[20] = new SqlParameter("@Politics", SqlDbType.NVarChar, 50);
            if (data.PoliticsIsNull)
            {
                commandParameters[20].Value = DBNull.Value;
            }
            else
            {
                commandParameters[20].Value = data.Politics;
            }
            commandParameters[0x15] = new SqlParameter("@HomeAddr", SqlDbType.NVarChar, 200);
            if (data.HomeAddrIsNull)
            {
                commandParameters[0x15].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x15].Value = data.HomeAddr;
            }
            commandParameters[0x16] = new SqlParameter("@HomeTel", SqlDbType.NVarChar, 50);
            if (data.HomeTelIsNull)
            {
                commandParameters[0x16].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x16].Value = data.HomeTel;
            }
            commandParameters[0x17] = new SqlParameter("@Email", SqlDbType.NVarChar, 200);
            if (data.EmailIsNull)
            {
                commandParameters[0x17].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x17].Value = data.Email;
            }
            commandParameters[0x18] = new SqlParameter("@ContractType", SqlDbType.NVarChar, 50);
            if (data.ContractTypeIsNull)
            {
                commandParameters[0x18].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x18].Value = data.ContractType;
            }
            commandParameters[0x19] = new SqlParameter("@ContractDate1", SqlDbType.DateTime);
            if (data.ContractDate1IsNull)
            {
                commandParameters[0x19].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x19].Value = data.ContractDate1;
            }
            commandParameters[0x1a] = new SqlParameter("@ContractDate2", SqlDbType.DateTime);
            if (data.ContractDate2IsNull)
            {
                commandParameters[0x1a].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x1a].Value = data.ContractDate2;
            }
            commandParameters[0x1b] = new SqlParameter("@LeaveType", SqlDbType.NVarChar, 50);
            if (data.LeaveTypeIsNull)
            {
                commandParameters[0x1b].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x1b].Value = data.LeaveType;
            }
            commandParameters[0x1c] = new SqlParameter("@PostChange", SqlDbType.NText);
            if (data.PostChangeIsNull)
            {
                commandParameters[0x1c].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x1c].Value = data.PostChange;
            }
            commandParameters[0x1d] = new SqlParameter("@Edu", SqlDbType.NText);
            if (data.EduIsNull)
            {
                commandParameters[0x1d].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x1d].Value = data.Edu;
            }
            commandParameters[30] = new SqlParameter("@Resume", SqlDbType.NText);
            if (data.ResumeIsNull)
            {
                commandParameters[30].Value = DBNull.Value;
            }
            else
            {
                commandParameters[30].Value = data.Resume;
            }
            commandParameters[0x1f] = new SqlParameter("@Sociaty", SqlDbType.NText);
            if (data.SociatyIsNull)
            {
                commandParameters[0x1f].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x1f].Value = data.Sociaty;
            }
            commandParameters[0x20] = new SqlParameter("@Reward", SqlDbType.NText);
            if (data.RewardIsNull)
            {
                commandParameters[0x20].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x20].Value = data.Reward;
            }
            commandParameters[0x21] = new SqlParameter("@Certificate", SqlDbType.NText);
            if (data.CertificateIsNull)
            {
                commandParameters[0x21].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x21].Value = data.Certificate;
            }
            commandParameters[0x22] = new SqlParameter("@Train", SqlDbType.NText);
            if (data.TrainIsNull)
            {
                commandParameters[0x22].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x22].Value = data.Train;
            }
            commandParameters[0x23] = new SqlParameter("@Surety", SqlDbType.NText);
            if (data.SuretyIsNull)
            {
                commandParameters[0x23].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x23].Value = data.Surety;
            }
            commandParameters[0x24] = new SqlParameter("@Contract", SqlDbType.NText);
            if (data.ContractIsNull)
            {
                commandParameters[0x24].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x24].Value = data.Contract;
            }
            commandParameters[0x25] = new SqlParameter("@Insure", SqlDbType.NText);
            if (data.InsureIsNull)
            {
                commandParameters[0x25].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x25].Value = data.Insure;
            }
            commandParameters[0x26] = new SqlParameter("@BodyExamim", SqlDbType.NText);
            if (data.BodyExamimIsNull)
            {
                commandParameters[0x26].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x26].Value = data.BodyExamim;
            }
            commandParameters[0x27] = new SqlParameter("@Memo", SqlDbType.NText);
            if (data.MemoIsNull)
            {
                commandParameters[0x27].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x27].Value = data.Memo;
            }
            commandParameters[40] = new SqlParameter("@Photo", SqlDbType.Image);
            if (data.PhotoIsNull)
            {
                commandParameters[40].Value = DBNull.Value;
            }
            else
            {
                commandParameters[40].Value = data.Photo;
            }
            commandParameters[0x29] = new SqlParameter("@AttachmentId", SqlDbType.NVarChar, 50);
            if (data.AttachmentIdIsNull)
            {
                commandParameters[0x29].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x29].Value = data.AttachmentId;
            }
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }
    }
}

